<?php

class StudentQuery_AgeController extends Zend_Controller_Action
{
    protected $_year;
    protected $_default_age_date;
    public function init()
    {

    }

    public function indexAction()
    {
        /* Initialize action controller here */
        $year = $this->view->Semester['year'];
        $semester = $this->view->Semester['semester'];
        // 參數
        $moduleParam = new Default_Model_ModulesParam();
        $params = $moduleParam->getParamByResourceId('StudentQuery');
        $defaultAgeDate = $params['default_age_date'];
        $ageStart = $params['age_start'] ;
        $ageEnd = $params['age_end'];
        $this->view->defaultAgeDate = $defaultAgeDate;



        $listArr = array();
        $tolArr = array();
        $tolYear = array();
        $tolYearClass = array();
        $tolSex = array();
        //上次歲數
        $preYear = '';
        for($i=$ageStart ; $i <= $ageEnd+1; $i++) {
            $yy = ($year+1911- $i)."-".$defaultAgeDate ;

            $q = Doctrine_Query::create()
            ->select('a.sex, COUNT(a.sn) AS cc, b.grade AS grade')
            ->from('Student_Model_Student a')
            ->leftJoin('a.Student_Model_StudentSemester b')
            ->whereIN('a.study_condition',
            array(Student_Model_StudentTransfer::IN_STADY,
            Student_Model_StudentTransfer::HOMESCHOOLING))
            ->andWhere('b.year=?', $year)
            ->andWhere('b.semester=?', $semester);
            if ($preYear == '')
            $q->andWhere('a.birthday >=?',$yy);
            elseif ($i==$ageEnd+1)
            $q->andWhere('a.birthday < ?', $yy);
            else
            $q->andWhere('a.birthday >= ? AND a.birthday < ?',array($yy, $preYear));

            $q->groupBy('b.grade, a.sex');
            $res = $q->fetchArray();
            foreach($res as $row) {
                if ($row['sex']=='M') $sex=1 ; else $sex =2;

                if (! isset( $tolAll[$sex] ))
                $tolAll[$sex]  = 0;

                if (! isset($tolAll["all"]))
                $tolAll["all"]  = 0;

                if (! isset ($tolYear[$i] ))
                $tolYear[$i]  = 0;

                if (! isset($tolYearClass[$row["grade"]][$sex]))
                $tolYearClass[$row["grade"]][$sex] = 0;

                if (! isset( $tolSex[$i][$sex]))
                $tolSex[$i][$sex]  = 0 ;

                if (! isset($tolYear["all"] ))
                $tolYear["all"]  = 0;

                $listArr[$row["grade"]][$sex][$i] = $row["cc"];
                //人數加總
                $tolAll["all"] += $row["cc"];
                $tolAll[$sex] += $row["cc"];
                //歲數加總
                $tolYear["all"] += $row["cc"];
                $tolYear[$i] += $row["cc"];
                $tolYearClass[$row["grade"]][$sex] += $row["cc"];
                $tolSex[$i][$sex] += $row["cc"];

            }
            $preYear = $yy;

        }

        $this->view->sexName = array('1'=>'boy', '2'=>'girl');
        $this->view->listArr = $listArr;
        $this->view->tolAll = $tolAll;
        $this->view->tolYear = $tolYear;
        $this->view->tolYearClass = $tolYearClass;
        $this->view->tolSex = $tolSex;
    }

    function getDetailAction()
    {
        /* Initialize action controller here */
        $year = $this->view->Semester['year'];
        $semester = $this->view->Semester['semester'];
        // 參數
        $moduleParam = new Default_Model_ModulesParam();
        $params = $moduleParam->getParamByResourceId('StudentQuery');
        $defaultAgeDate = $params['default_age_date'];

        $this->getHelper('layout')->disableLayout();
        $id = $this->_getParam('id');
        $arr = explode("-", $id);
        $currAge =  ($year+1911- $arr[1])."-".$defaultAgeDate ;
        $preAge =  ($year+1912- $arr[1])."-".$defaultAgeDate ;
        $q = Doctrine_Query::create()
        ->select('a.name, a.sex, a.birthday, b.student_id AS student_id,
        b.grade AS grade, b.class_sn AS class_sn , b.number AS number')
        ->from('Student_Model_Student a')
        ->leftJoin('a.Student_Model_StudentSemester b')
        ->whereIN('a.study_condition',
        array(Student_Model_StudentTransfer::IN_STADY,
        Student_Model_StudentTransfer::HOMESCHOOLING))
        ->andWhere('b.year=?', $year)
        ->andWhere('b.semester=?', $semester);
        if ($arr[1] != 'all')
        $q->andWHere('a.birthday>=? AND a.birthday<?',array($currAge , $preAge));
        if ($arr[2] == 1)
        $q->andWhere('a.sex = ?', 'M');
        elseif ($arr[2] == 2)
        $q->andWhere('a.sex = ?', 'F');
        if ($arr[0] == 'detail')
        $q->andWHere('b.grade=?', (int)$arr[3]);
        $q->orderBY('b.grade, b.class_sn,b.number');
        $this->view->data = $q->fetchArray();
        //班級陣列
        $semesterClass = new Semester_Model_SemesterClass();
        $this->view->className = $semesterClass->getClassNames($year,$semester);

    }

}

